CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_REAL4_TRANSACTION" is

  --------------------------------------------------------------------------------------------------

  -- 向real4_transaction_condition表中写数据
  /*
  1.判断这只股票在指定时间范围内是否出现了除权
  2.当前收盘价与某段时间最高价的百分比
  3.当前收盘价与某段时间最低价的百分比
  4.判断当前收盘价是否在某个价格区间之中
  5.计算下一个交易日出现金叉或死叉的阈值
  6.250日、120日均线不单调递减
  7.250日、120日均线不单调递增
  
  p_ma120_not_decrea为1时表示判断120均线是否不单调递减，p_ma120_not_decrea_date_number表示120日均线多少天内不单调递减；
  p_ma250_not_decrea为1时表示判断250均线是否不单调递减，p_ma250_not_decrea_date_number表示250日均线多少天内不单调递减。
  p_ma120_not_increa为1时表示判断120均线是否不单调递增，p_ma120_not_increa_date_number表示120日均线多少天内不单调递增；
  p_ma250_not_increa为1时表示判断250均线是否不单调递增，p_ma250_not_increa_date_number表示250日均线多少天内不单调递增。
  */
  procedure write_real4_transac_cond(p_begin_date in varchar2,
                                     p_date       in varchar2,
                                     /*p_less_than_percentage         in number,
                                                                                                                                                                                     p_more_than_percentage         in number,
                                                                                                                                                                                     p_close_price_start            in number,
                                                                                                                                                                                     p_close_price_end              in number,*/
                                     p_ma120_not_decrea             in number,
                                     p_ma120_not_decrea_date_number in number,
                                     p_ma250_not_decrea             in number,
                                     p_ma250_not_decrea_date_number in number,
                                     p_ma120_not_increa             in number,
                                     p_ma120_not_increa_date_number in number,
                                     p_ma250_not_increa             in number,
                                     p_ma250_not_increa_date_number in number) as
  begin
    declare
      -- 股票代码
      v_code varchar2(10);
      -- 某只股票最近一天的交易记录
      rowtype_lastest_s_t_d stock_transaction_data%rowtype;
      -- 是否是金叉或死叉的阈值（收盘价）
      v_threshold number;
      -- MACD金叉时的收盘价
      v_macd_gold_cross_close_price number;
      -- MACD死叉时的收盘价
      v_macd_dead_cross_close_price number;
      -- 收盘价金叉MA5时的收盘价
      v_c_p_ma5_g_c_close_price number;
      -- 收盘价死叉MA5时的收盘价
      v_c_p_ma5_d_c_close_price number;
      -- hei_kin_ashi上升趋势时的hei_kin_ashi开盘价
      v_h_k_a_up_h_k_a_open_price number;
      -- hei_kin_ashi下跌趋势时的hei_kin_ashi开盘价
      v_h_k_a_down_h_k_a_open_price number;
      -- kd金叉时的RSV
      v_kd_gold_cross_rsv number;
      -- kd死叉时的RSV
      v_kd_dead_cross_rsv number;
      -- 表示是否除过权。1表示除过，0表示没有
      v_is_xr number;
      -- 当前收盘价
      v_current_close_price number;
      -- 某段时间最高价
      v_history_highest_price number;
      -- 某段时间最低价
      v_history_lowest_price number;
      -- 记录数
      v_record_number number;
      -- 表示当前收盘价是否在某段时间最高价的百分比之下
      v_is_less_than_percentage number;
      -- 表示当前收盘价是否在某段时间最低价的百分比之上
      v_is_more_than_percentage number;
      -- 表示当前收盘价是否在某个价格之下
      v_is_between_close_price number;
      -- 表示120日均线是否不单调递减
      v_is_ma120_not_decreasing number;
      -- 表示250日均线是否不单调递减
      v_is_ma250_not_decreasing number;
      -- 表示120日均线是否不单调递增
      v_is_ma120_not_increasing number;
      -- 表示250日均线是否不单调递增
      v_is_ma250_not_increasing number;
      -- 下一日的120日均线（单调不递减）
      v_next_ma120_not_decreasing number;
      -- 下一日的250日均线（单调不递减）
      v_next_ma250_not_decreasing number;
      -- 下一日的120日均线（单调不递增）
      v_next_ma120_not_increasing number;
      -- 下一日的250日均线（单调不递增）
      v_next_ma250_not_increasing number;
      -- 查询所有股票的code_字段
      cursor cur_stock_code is
        select distinct t.code_ from stock_transaction_data_all t;
      -- 注意：下面的存储过程里必须减一，否则到下面的SQL时，计算的总天数就会多出一天
      -- 根据条件code、date，查找stock_transaction_data_all表，按降序排列，只取前p_ma120_not_decrea_date_number条记录
      cursor cur_ma120_not_decreasing is
        select *
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= p_ma120_not_decrea_date_number - 1;
      -- 根据条件code、date，查找stock_transaction_data_all表，按降序排列，只取前p_ma250_not_decrea_date_number条记录
      cursor cur_ma250_not_decreasing is
        select *
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= p_ma250_not_decrea_date_number - 1;
      -- 根据条件code、date，查找stock_transaction_data_all表，按降序排列，只取前p_ma120_not_increa_date_number条记录
      cursor cur_ma120_not_increasing is
        select *
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= p_ma120_not_increa_date_number - 1;
      -- 根据条件code、date，查找stock_transaction_data_all表，按降序排列，只取前p_ma250_not_increa_date_number条记录
      cursor cur_ma250_not_increasing is
        select *
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = v_code
                   and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                 order by t.date_ desc) b
         where rownum <= p_ma250_not_increa_date_number - 1;
    begin
      for row_cur_stock_code in cur_stock_code loop
        v_code := row_cur_stock_code.code_;
      
        -- 重置
        v_macd_gold_cross_close_price := null;
        v_macd_dead_cross_close_price := null;
        v_is_xr                       := null;
        v_is_between_close_price      := null;
        v_is_less_than_percentage     := null;
        v_is_more_than_percentage     := null;
        v_is_ma120_not_decreasing     := null;
        v_is_ma250_not_decreasing     := null;
        v_c_p_ma5_g_c_close_price     := null;
        v_c_p_ma5_d_c_close_price     := null;
        v_h_k_a_up_h_k_a_open_price   := null;
        v_h_k_a_down_h_k_a_open_price := null;
        v_kd_gold_cross_rsv           := null;
        v_kd_dead_cross_rsv           := null;
        v_is_ma120_not_increasing     := null;
        v_is_ma250_not_increasing     := null;
      
        -- 查找某只股票按照降序排列的第一条交易记录。因为可能之前有停牌，所以此处是小于等于
        begin
          select *
            into rowtype_lastest_s_t_d
            from (select *
                    from stock_transaction_data_all t
                   where t.code_ = row_cur_stock_code.code_
                     and t.date_ <= to_date(p_date, 'yyyy-mm-dd')
                   order by t.date_ desc) std
           where rownum <= 1;
        exception
          when no_data_found then
            continue;
        end;
      
        ----------------- 120日均线不单调递减 -----------------
        if p_ma120_not_decrea = 1 then
          -- 重新设置为1
          v_is_ma120_not_decreasing := 1;
          for ma120_not_decreasing in cur_ma120_not_decreasing loop
            begin
              -- 返回游标cur_ma120_not_decreasing下一条记录的ma5字段
              select b.ma120
                into v_next_ma120_not_decreasing
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = row_cur_stock_code.code_
                         and t.date_ < ma120_not_decreasing.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果没有120日均线，或者出现了递减的情况，则直接跳出这个循环
              if v_next_ma120_not_decreasing is null or
                 v_next_ma120_not_decreasing > ma120_not_decreasing.ma120 then
                v_is_ma120_not_decreasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_ma120_date_number个，则也认为是不满足不单调递减条件
                v_is_ma120_not_decreasing := -1;
                /*DBMS_OUTPUT.put_line('判断120日均线是否不单调递减时，股票' ||
                row_cur_stock_code.code_ || '均线数量不足' ||
                p_ma120_date_number ||
                '个，则也认为是不满足不单调递减条件');*/
                exit;
            end;
          end loop;
        end if;
      
        ----------------- 250日均线不单调递减 -----------------
        if p_ma250_not_decrea = 1 then
          -- 重新设置为1
          v_is_ma250_not_decreasing := 1;
          for ma250_not_decreasing in cur_ma250_not_decreasing loop
            begin
              -- 返回游标cur_ma250_not_decreasing下一条记录的ma5字段
              select b.ma250
                into v_next_ma250_not_decreasing
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = row_cur_stock_code.code_
                         and t.date_ < ma250_not_decreasing.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果没有250日均线，或者出现了递减的情况，则直接跳出这个循环
              if v_next_ma250_not_decreasing is null or
                 v_next_ma250_not_decreasing > ma250_not_decreasing.ma250 then
                v_is_ma250_not_decreasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_ma120_date_number个，则也认为是不满足不单调递减条件
                v_is_ma250_not_decreasing := -1;
                /*DBMS_OUTPUT.put_line('判断250日均线是否不单调递减时，股票' ||
                row_cur_stock_code.code_ || '均线数量不足' ||
                p_ma250_date_number ||
                '个，则也认为是不满足不单调递减条件');*/
                exit;
            end;
          end loop;
        end if;
      
        ----------------- 120日均线不单调递增 -----------------
        if p_ma120_not_increa = 1 then
          -- 重新设置为1
          v_is_ma120_not_increasing := 1;
          for ma120_not_increasing in cur_ma120_not_increasing loop
            begin
              -- 返回游标cur_ma120_not_increasing下一条记录的ma5字段
              select b.ma120
                into v_next_ma120_not_increasing
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = row_cur_stock_code.code_
                         and t.date_ < ma120_not_increasing.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果没有120日均线，或者出现了递增的情况，则直接跳出这个循环
              if v_next_ma120_not_increasing is null or
                 v_next_ma120_not_increasing < ma120_not_increasing.ma120 then
                v_is_ma120_not_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_ma120_not_increa_date_number个，则也认为是不满足不单调递增条件
                v_is_ma120_not_increasing := -1;
                /*DBMS_OUTPUT.put_line('判断120日均线是否不单调递增时，股票' ||
                row_cur_stock_code.code_ || '均线数量不足' ||
                p_ma120_not_increa_date_number ||
                '个，则也认为是不满足不单调递增条件');*/
                exit;
            end;
          end loop;
        end if;
      
        ----------------- 250日均线不单调递增 -----------------
        if p_ma250_not_increa = 1 then
          -- 重新设置为1
          v_is_ma250_not_increasing := 1;
          for ma250_not_increasing in cur_ma250_not_increasing loop
            begin
              -- 返回游标cur_ma250_not_increasing下一条记录的ma5字段
              select b.ma250
                into v_next_ma250_not_increasing
                from (select *
                        from stock_transaction_data_all t
                       where t.code_ = row_cur_stock_code.code_
                         and t.date_ < ma250_not_increasing.date_
                       order by t.date_ desc) b
               where rownum <= 1;
            
              -- 如果没有250日均线，或者出现了递增的情况，则直接跳出这个循环
              if v_next_ma250_not_increasing is null or
                 v_next_ma250_not_increasing < ma250_not_increasing.ma250 then
                v_is_ma250_not_increasing := -1;
                exit;
              end if;
            exception
              when no_data_found then
                -- 如果均线数量不足p_ma120_not_increa_date_number个，则也认为是不满足不单调递增条件
                v_is_ma250_not_increasing := -1;
                /*DBMS_OUTPUT.put_line('判断250日均线是否不单调递增时，股票' ||
                row_cur_stock_code.code_ || '均线数量不足' ||
                p_ma250_not_increa_date_number ||
                '个，则也认为是不满足不单调递增条件');*/
                exit;
            end;
          end loop;
        end if;
      
        ----------------- 判断这只股票在指定时间范围内是否出现了除权 -----------------
        if fnc_stock_xr(row_cur_stock_code.code_, p_begin_date, p_date) = 1 then
          v_is_xr := 1;
        else
          v_is_xr := 0;
        end if;
      
        ----------------- 当前收盘价与某段时间最高价的百分比 -----------------
        -- 当前收盘价
        /*select count(*)
          into v_record_number
          from stock_transaction_data t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        
        if v_record_number = 0 then
          continue;
        end if;
        
        select t.close_price
          into v_current_close_price
          from stock_transaction_data t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        
        -- 某段时间最高价
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd');
        
        if v_record_number = 0 then
          continue;
        end if;
        
        select max(t.highest_price)
          into v_history_highest_price
          from stock_transaction_data_all t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd');
        
        -- 判断当前收盘价是否在某段时间最高价的百分比之下
        if (v_current_close_price / v_history_highest_price * 100) <=
           p_less_than_percentage then
          v_is_less_than_percentage := 1;
        else
          v_is_less_than_percentage := 0;
        end if;
        
        ----------------- 当前收盘价与某段时间最低价的百分比 -----------------
        -- 当前收盘价
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        
        if v_record_number = 0 then
          continue;
        end if;
        
        select t.close_price
          into v_current_close_price
          from stock_transaction_data_all t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ = to_date(p_date, 'yyyy-mm-dd');
        
        -- 某段时间最低价
        select count(*)
          into v_record_number
          from stock_transaction_data_all t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd');
        
        if v_record_number = 0 then
          continue;
        end if;
        
        select min(t.lowest_price)
          into v_history_lowest_price
          from stock_transaction_data_all t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ >= to_date(p_begin_date, 'yyyy-mm-dd');
        
        -- 判断当前收盘价是否在某段时间最低价的百分比之上
        if (v_current_close_price / v_history_lowest_price * 100) >=
           p_more_than_percentage then
          v_is_more_than_percentage := 1;
        else
          v_is_more_than_percentage := 0;
        end if;*/
      
        ----------------- 判断当前收盘价是否在某个价格区间之中 -----------------
        /*if rowtype_lastest_s_t_d.close_price > p_close_price_start and
           rowtype_lastest_s_t_d.close_price < p_close_price_end then
          v_is_between_close_price := 1;
        else
          v_is_between_close_price := 0;
        end if;*/
      
        ----------------- 计算下一个交易日MACD出现金叉或死叉的阈值 -----------------
        select (t.ema12 * (11 / 13 * 2 / 10 - 11 / 13) +
               t.ema26 * (25 / 27 - 25 / 27 * 2 / 10) + t.dea * 8 / 10) /
               (2 / 13 - 2 / 27 - 2 / 13 * 2 / 10 + 2 / 27 * 2 / 10)
          into v_threshold
          from stock_transaction_data_all t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ = rowtype_lastest_s_t_d.date_;
      
        if rowtype_lastest_s_t_d.dif >= rowtype_lastest_s_t_d.dea then
          -- 已经是金叉状态，保存出现死叉时的收盘价
          v_macd_dead_cross_close_price := v_threshold;
          --v_macd_gold_cross_close_price := null;
        end if;
        if rowtype_lastest_s_t_d.dif <= rowtype_lastest_s_t_d.dea then
          -- 已经是死叉状态，保存出现金叉时的收盘价
          --v_macd_dead_cross_close_price := null;
          v_macd_gold_cross_close_price := v_threshold;
        end if;
        /*if rowtype_lastest_s_t_d.dif = rowtype_lastest_s_t_d.dea then
          -- 状态不明，有可能是金叉状态，也有可能是死叉状态，同时保存出现金叉时的收盘价和死叉时的收盘价
          v_macd_dead_cross_close_price := v_threshold;
          v_macd_gold_cross_close_price := v_threshold;
        end if;*/
      
        ----------------- 计算下一个交易日close_price出现金叉或死叉ma5的阈值 -----------------
        -- (a+b+c+d+e)/5=MA5<e
        select sum(t1.close_price) / 4
          into v_threshold
          from (select *
                  from stock_transaction_data_all t
                 where t.code_ = row_cur_stock_code.code_
                   and t.date_ <= rowtype_lastest_s_t_d.date_
                 order by t.date_ desc) t1
         where rownum <= 4;
      
        if rowtype_lastest_s_t_d.close_price >= rowtype_lastest_s_t_d.ma5 then
          -- 已经是金叉状态，保存出现死叉时的收盘价
          v_c_p_ma5_d_c_close_price := v_threshold;
          --v_c_p_ma5_g_c_close_price := null;
        end if;
        if rowtype_lastest_s_t_d.close_price <= rowtype_lastest_s_t_d.ma5 then
          -- 已经是死叉状态，保存出现金叉时的收盘价
          --v_c_p_ma5_d_c_close_price := null;
          v_c_p_ma5_g_c_close_price := v_threshold;
        end if;
        /*if rowtype_lastest_s_t_d.close_price = rowtype_lastest_s_t_d.ma5 then
          -- 状态不明，有可能是金叉状态，也有可能是死叉状态，同时保存出现金叉时的收盘价和死叉时的收盘价
          v_c_p_ma5_d_c_close_price := v_threshold;
          v_c_p_ma5_g_c_close_price := v_threshold;
        end if;*/
      
        ----------------- 计算下一个交易日hei_kin_ashi出现上涨趋势或下跌趋势的阈值 -----------------
        -- 如果前一天的ha_open_price和ha_close_price为空的话，则使用open_price和close_price
        -- 因为可能之前有停牌，所以此处是小于等于
        begin
          select (t1.ha_open_price + t1.ha_close_price) / 2
            into v_threshold
            from (select *
                    from stock_transaction_data_all t
                   where t.code_ = row_cur_stock_code.code_
                     and t.date_ <= rowtype_lastest_s_t_d.date_
                   order by t.date_ desc) t1
           where rownum <= 1;
        exception
          when others then
            v_threshold := null;
        end;
      
        if rowtype_lastest_s_t_d.ha_close_price >
           rowtype_lastest_s_t_d.ha_open_price then
          -- 已经是上涨趋势，保存出现下跌趋势时的开盘价
          v_h_k_a_down_h_k_a_open_price := v_threshold;
          --v_h_k_a_up_h_k_a_open_price   := null;
        end if;
        if rowtype_lastest_s_t_d.ha_close_price <
           rowtype_lastest_s_t_d.ha_open_price then
          -- 已经是下跌趋势，保存出现上涨趋势时的开盘价
          --v_h_k_a_down_h_k_a_open_price := null;
          v_h_k_a_up_h_k_a_open_price := v_threshold;
        end if;
        /*if rowtype_lastest_s_t_d.ha_close_price =
           rowtype_lastest_s_t_d.ha_open_price then
          -- 状态不明，有可能是上涨趋势，也有可能是死下跌趋势，同时保存出现上涨趋势时的收盘价和下跌趋势时的收盘价
          v_h_k_a_down_h_k_a_open_price := v_threshold;
          v_h_k_a_up_h_k_a_open_price := v_threshold;
        end if;*/
      
        ----------------- 计算下一个交易日KD出现金叉或死叉的阈值 -----------------
        select (2 / 3 * t.d - 4 / 9 * t.k) * 9 / 2
          into v_threshold
          from stock_transaction_data_all t
         where t.code_ = row_cur_stock_code.code_
           and t.date_ = rowtype_lastest_s_t_d.date_;
      
        if rowtype_lastest_s_t_d.k >= rowtype_lastest_s_t_d.d then
          -- 已经是金叉状态，保存出现死叉时的RSV
          v_kd_dead_cross_rsv := v_threshold;
          --v_kd_gold_cross_rsv := null;
        end if;
        if rowtype_lastest_s_t_d.k <= rowtype_lastest_s_t_d.d then
          -- 已经是死叉状态，保存出现金叉时的RSV
          --v_kd_dead_cross_rsv := null;
          v_kd_gold_cross_rsv := v_threshold;
        end if;
        /*if rowtype_lastest_s_t_d.k = rowtype_lastest_s_t_d.d then
          -- 状态不明，有可能是金叉状态，也有可能是死叉状态，同时保存出现金叉时的收盘价和死叉时的RSV
          v_kd_dead_cross_rsv := v_threshold;
          v_kd_gold_cross_rsv := v_threshold;
        end if;*/
      
        ---------------------------------- 保存记录 ----------------------------------
        insert into real4_transaction_condition
          (STOCK_CODE,
           TRANSACTION_DATE,
           CALCULATE_DATE,
           MACD_GOLD_CROSS_CLOSE_PRICE,
           MACD_DEAD_CROSS_CLOSE_PRICE,
           IS_XR,
           IS_BETWEEN_CLOSE_PRICE,
           IS_LESS_THAN_PERCENTAGE,
           IS_MORE_THAN_PERCENTAGE,
           IS_MA_1_2_0_NOT_DECREASING,
           IS_MA_2_5_0_NOT_DECREASING,
           C_P_MA_5_G_C_CLOSE_PRICE,
           C_P_MA_5_D_C_CLOSE_PRICE,
           H_K_A_UP_H_K_A_OPEN_PRICE,
           H_K_A_DOWN_H_K_A_OPEN_PRICE,
           KD_GOLD_CROSS_RSV,
           KD_DEAD_CROSS_RSV,
           IS_MA_1_2_0_NOT_INCREASING,
           IS_MA_2_5_0_NOT_INCREASING)
        values
          (rowtype_lastest_s_t_d.code_,
           rowtype_lastest_s_t_d.date_,
           sysdate,
           v_macd_gold_cross_close_price,
           v_macd_dead_cross_close_price,
           v_is_xr,
           v_is_between_close_price,
           v_is_less_than_percentage,
           v_is_more_than_percentage,
           v_is_ma120_not_decreasing,
           v_is_ma250_not_decreasing,
           v_c_p_ma5_g_c_close_price,
           v_c_p_ma5_d_c_close_price,
           v_h_k_a_up_h_k_a_open_price,
           v_h_k_a_down_h_k_a_open_price,
           v_kd_gold_cross_rsv,
           v_kd_dead_cross_rsv,
           v_is_ma120_not_increasing,
           v_is_ma250_not_increasing);
        commit;
      end loop;
    end;
  end write_real4_transac_cond;

end pkg_real4_transaction;